/********************************************************************
FileName    :   Dal_Departments.cs
CreateTime  :   2015-10-23 10:10
BaseClass   :   Dal_Departments
FileType    :   .cs
Author      :  minjie
Description :   Dal_DepartmentsDAL层（简单三层）
*********************************************************************/

using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Data;
namespace OS.DAL
{
	/// <summary>
	/// Dal_Departments的摘要说明。
	/// </summary>
	public partial class Dal_Departments 
  	{ 
     		      
  		 
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int DeptId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Departments");
            strSql.Append(" where DeptId=@DeptId");
            SqlParameter[] parameters = {
					new SqlParameter("@DeptId", SqlDbType.Int,4)
			};
            parameters[0].Value = DeptId;

            return SQLHelper.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据名称 获取部门ID 
        /// </summary>
        /// <returns></returns>
        public int GetDepartmentsIDByName(string name)
        {
            string sql = "select DeptId  from dbo.Departments where DeptName='" + name + "'";
            object obj = SQLHelper.GetSingle(sql);

            if (obj != null && obj.ToString() != "")
                return int.Parse(obj.ToString());
            else
                return 0;
        } 
  		 
  		/// <summary> 
  		/// 增加一条数据 
  		/// </summary> 
  		public  bool  Add(Departments_Entity model) 
  		{ 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("insert into Departments(");			 
  	strSql.Append("DeptName,ParentId,DeptLevel,CreateBy,UserId,CreateTime,isOrgan) values ("); 
  	strSql.Append("	@DeptName,@ParentId,@DeptLevel,@CreateBy,@UserId,@CreateTime,@isOrgan	 ) "); 
  			SqlParameter[] parameters = { 
              new SqlParameter("@DeptName", SqlDbType.NVarChar,255) ,              
              new SqlParameter("@ParentId", SqlDbType.Int,4) ,              
              new SqlParameter("@DeptLevel", SqlDbType.Int,4) ,              
              new SqlParameter("@CreateBy", SqlDbType.NVarChar,255) ,              
              new SqlParameter("@UserId", SqlDbType.Int,4) ,              
              new SqlParameter("@CreateTime", SqlDbType.DateTime) ,              
              new SqlParameter("@isOrgan", SqlDbType.Int,4)};             
           if (model.DeptName != null)  
              parameters[0].Value = model.DeptName;  
           else  
              parameters[0].Value = DBNull.Value;  
              
           if (model.ParentId != null)  
              parameters[1].Value = model.ParentId;  
           else  
              parameters[1].Value = DBNull.Value;  
              
           if (model.DeptLevel != null)  
              parameters[2].Value = model.DeptLevel;  
           else  
              parameters[2].Value = DBNull.Value;  
              
           if (model.CreateBy != null)  
              parameters[3].Value = model.CreateBy;  
           else  
              parameters[3].Value = DBNull.Value;  
              
           if (model.UserId != null)  
              parameters[4].Value = model.UserId;  
           else  
              parameters[4].Value = DBNull.Value;  
              
           if (model.CreateTime != null)  
              parameters[5].Value = model.CreateTime;  
           else  
              parameters[5].Value = DBNull.Value;  
              
           if (model.isOrgan != null)  
              parameters[6].Value = model.isOrgan;  
           else  
              parameters[6].Value = DBNull.Value;  
              
  			int rows=SQLHelper.ExecuteSql(strSql.ToString(),parameters);			 
  			if (rows > 0) 
  			{ 
  				return true; 
  			} 
  			else 
  			{ 
              return false;; 
           }  
  		} 
  		 
  		/// <summary> 
  		/// 增加一条数据(字符串) 
  		/// </summary> 
  		public  string  AddStr(Departments_Entity model) 
  		{ 
  				if(model.DeptId==null) 
  				{ 
  					model.DeptId=0; 
  				} 
  				if(model.DeptName==null) 
  				{ 
  					model.DeptName="";  
  				} 
  				if(model.ParentId==null) 
  				{ 
  					model.ParentId=0; 
  				} 
  				if(model.DeptLevel==null) 
  				{ 
  					model.DeptLevel=0; 
  				} 
  				if(model.CreateBy==null) 
  				{ 
  					model.CreateBy="";  
  				} 
  				if(model.UserId==null) 
  				{ 
  					model.UserId=0; 
  				} 
  				if(model.isOrgan==null) 
  				{ 
  					model.isOrgan=0; 
  				} 
  		 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("insert into Departments(");			 
  	strSql.Append("DeptName,ParentId,DeptLevel,CreateBy,UserId,CreateTime,isOrgan) values ("); 
  	strSql.Append("	"+ model.DeptId+",'"+ model.DeptName+"',"+ model.ParentId+","+ model.DeptLevel+",'"+ model.CreateBy+"',"+ model.UserId+",'"+ model.CreateTime+"'," +model.isOrgan+"	 ) "); 
           return strSql.ToString(); 
  		} 
  		 
  		 
  		/// <summary> 
  		/// 更新一条数据 
  		/// </summary> 
  		public bool Update(Departments_Entity model) 
  		{ 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("update Departments set "); 
              strSql.Append(" DeptName = @DeptName,"); 
              strSql.Append(" ParentId = @ParentId,"); 
              strSql.Append(" DeptLevel = @DeptLevel,"); 
              strSql.Append(" CreateBy = @CreateBy,"); 
              strSql.Append(" UserId = @UserId,"); 
              strSql.Append(" CreateTime = @CreateTime,"); 
              strSql.Append(" isOrgan = @isOrgan"); 
  			strSql.Append(" where  DeptId=@DeptId "); 
  						 
  SqlParameter[] parameters = { 
              new SqlParameter("@DeptId", SqlDbType.Int,4) ,              
              new SqlParameter("@DeptName", SqlDbType.NVarChar,255) ,              
              new SqlParameter("@ParentId", SqlDbType.Int,4) ,              
              new SqlParameter("@DeptLevel", SqlDbType.Int,4) ,              
              new SqlParameter("@CreateBy", SqlDbType.NVarChar,255) ,              
              new SqlParameter("@UserId", SqlDbType.Int,4) ,              
              new SqlParameter("@CreateTime", SqlDbType.DateTime) ,              
              new SqlParameter("@isOrgan", SqlDbType.Int,4)};             
           if (model.DeptId != null)  
              parameters[0].Value = model.DeptId;  
           else  
              parameters[0].Value = DBNull.Value;  
              
           if (model.DeptName != null)  
              parameters[1].Value = model.DeptName;  
           else  
              parameters[1].Value = DBNull.Value;  
              
           if (model.ParentId != null)  
              parameters[2].Value = model.ParentId;  
           else  
              parameters[2].Value = DBNull.Value;  
              
           if (model.DeptLevel != null)  
              parameters[3].Value = model.DeptLevel;  
           else  
              parameters[3].Value = DBNull.Value;  
              
           if (model.CreateBy != null)  
              parameters[4].Value = model.CreateBy;  
           else  
              parameters[4].Value = DBNull.Value;  
              
           if (model.UserId != null)  
              parameters[5].Value = model.UserId;  
           else  
              parameters[5].Value = DBNull.Value;  
              
           if (model.CreateTime != null)  
              parameters[6].Value = model.CreateTime;  
           else  
              parameters[6].Value = DBNull.Value;  
              
           if (model.isOrgan != null)  
              parameters[7].Value = model.isOrgan;  
           else  
              parameters[7].Value = DBNull.Value;  
              
              int rows=SQLHelper.ExecuteSql(strSql.ToString(),parameters); 
  			if (rows > 0) 
  			{ 
  				return true; 
  			} 
  			else 
  			{ 
  				return false; 
  			} 
  		} 
  		 
  		 
  		/// <summary> 
  		/// 删除一条数据 
  		/// </summary> 
  		public bool Delete(int  DeptId) 
  		{ 
  			 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("delete from Departments "); 
  			strSql.Append(" where  DeptId=@DeptId"); 
  			SqlParameter[] parameters = {  new SqlParameter("@DeptId", SqlDbType.Int,4) };  
    parameters[0].Value = DeptId;  
   
  			int rows=SQLHelper.ExecuteSql(strSql.ToString(),parameters); 
  			if (rows > 0) 
  			{ 
  				return true; 
  			} 
  			else 
  			{ 
  				return false; 
  			} 
  		} 
  		 
  		/// <summary> 
  		/// 批量删除一批数据 
  		/// </summary> 
  		public bool DeleteList(string DeptIdlist ) 
  		{ 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("delete from Departments "); 
  			strSql.Append(" where ID in ("+DeptIdlist + ")  "); 
  			int rows=SQLHelper.ExecuteSql(strSql.ToString()); 
  			if (rows > 0) 
  			{ 
  				return true; 
  			} 
  			else 
  			{ 
  				return false; 
  			} 
  		} 
  		 
  		 
  		/// <summary> 
  		/// 得到一个对象实体 
  		/// </summary> 
  		public Departments_Entity GetModel(int   DeptId) 
  		{ 
  			 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("select  DeptId,DeptName,ParentId,DeptLevel,CreateBy,UserId,CreateTime,isOrgan");	
		strSql.Append("  from Departments"); 
  			strSql.Append(" where  DeptId=@DeptId "); 
  			SqlParameter[] parameters = {  new SqlParameter("@DeptId", SqlDbType.Int,4) };  
    parameters[0].Value = DeptId;  
  			 
  			Departments_Entity model=new Departments_Entity(); 
  			DataSet ds=SQLHelper.Query(strSql.ToString(),parameters); 
  			 
  			if(ds!=null&&ds.Tables[0].Rows.Count>0) 
  			{ 
  		DataTable dt=ds.Tables[0];	  
  				if(dt.Rows[0]["DeptId"]!=null&&dt.Rows[0]["DeptId"].ToString()!="") 
  				{ 
  					model.DeptId=int.Parse(dt.Rows[0]["DeptId"].ToString()); 
  				} 
  				model.DeptName= dt.Rows[0]["DeptName"].ToString(); 
  				if(dt.Rows[0]["ParentId"]!=null&&dt.Rows[0]["ParentId"].ToString()!="") 
  				{ 
  					model.ParentId=int.Parse(dt.Rows[0]["ParentId"].ToString()); 
  				} 
  				if(dt.Rows[0]["DeptLevel"]!=null&&dt.Rows[0]["DeptLevel"].ToString()!="") 
  				{ 
  					model.DeptLevel=int.Parse(dt.Rows[0]["DeptLevel"].ToString()); 
  				} 
  				model.CreateBy= dt.Rows[0]["CreateBy"].ToString(); 
  				if(dt.Rows[0]["UserId"]!=null&&dt.Rows[0]["UserId"].ToString()!="") 
  				{ 
  					model.UserId=int.Parse(dt.Rows[0]["UserId"].ToString()); 
  				} 
  				if(dt.Rows[0]["CreateTime"]!=null&&dt.Rows[0]["CreateTime"].ToString()!="") 
  				{ 
  					model.CreateTime=DateTime.Parse(dt.Rows[0]["CreateTime"].ToString()); 
  				} 
  				if(dt.Rows[0]["isOrgan"]!=null&&dt.Rows[0]["isOrgan"].ToString()!="") 
  				{ 
  					model.isOrgan=int.Parse(dt.Rows[0]["isOrgan"].ToString()); 
  				} 
  				return model; 
  			} 
  			else 
  			{ 
  				return null; 
  			} 
  		} 
  		 
  		/// <summary> 
  		/// 分页获取数据列表 
  		/// </summary> 
  		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex) 
  		{ 
  			StringBuilder strSql=new StringBuilder(); 
  		 strSql.Append("SELECT * FROM ( "); 
  		 strSql.Append(" SELECT ROW_NUMBER() OVER ("); 
  if (!string.IsNullOrEmpty(orderby.Trim())) 
 { 
    strSql.Append("order by T." + orderby); 
 } 
else  
 { 
     strSql.Append("order by T.DeptId desc"); 
 } 
		 strSql.Append(")AS Row, T.*  from Departments T "); 
  		 if (!string.IsNullOrEmpty(strWhere.Trim()))  
  			{ 
  			 strSql.Append(" WHERE " + strWhere); 
  			} 
  		    strSql.Append(" ) TT"); 
  	        strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);			return SQLHelper.Query(strSql.ToString()); 
  		} 
  		 
  		 
  		/// <summary> 
  		/// 获得数据列表 
  		/// </summary> 
  		public DataSet GetList(string strWhere) 
  		{ 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("select * "); 
  			strSql.Append(" FROM Departments "); 
  			if(strWhere.Trim()!="") 
  			{ 
  				strSql.Append(" where "+strWhere); 
  			} 
  			return SQLHelper.Query(strSql.ToString()); 
  		} 
  		 
  		/// <summary> 
  		/// 获得前几行数据 
  		/// </summary> 
  		public DataSet GetList(int Top,string strWhere,string filedOrder) 
  		{ 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("select "); 
  			if(Top>0) 
  			{ 
  				strSql.Append(" top "+Top.ToString()); 
  			} 
  			strSql.Append(" * "); 
  			strSql.Append(" FROM Departments "); 
  			if(strWhere.Trim()!="") 
  			{ 
  				strSql.Append(" where "+strWhere); 
  			} 
  			strSql.Append(" order by " + filedOrder); 
  			return SQLHelper.Query(strSql.ToString()); 
  		} 
  		 
  		/// <summary> 
  		/// 获得记录条数 
  		/// </summary> 
  		public int GetRecordCount(string strWhere) 
  		{ 
  			StringBuilder strSql=new StringBuilder(); 
  			strSql.Append("select count(1) "); 
  			strSql.Append(" FROM Departments "); 
  			if(strWhere.Trim()!="") 
  			{ 
  				strSql.Append(" where "+strWhere); 
  			} 
  		   object obj=	SQLHelper.GetSingle(strSql.ToString()); 
  			if(obj!=null && obj.ToString()!="") 
  			{ 
              	return Convert.ToInt32(obj); 
  			} 
  			else 
  			return 0; 
  		} 
   
      
  	} 
  
}
